
-- 0 所有存储过程说明
IF(object_id('gf_info') is not null)
	drop proc gf_info
GO
create proc gf_info
AS
	select '0' 索引,'gf_info' 名称, '查看自定义存储过程' 功能
	union all select  1,'gf_random_num','获取随机数'
	union all select  2,'gf_table_info','查看表字段结构'
	union all select  3,'gf_length','统计长度'
	union all select  4,'gf_numbers','获取数字列表'
GO


-- 1 获取随机数
if (object_id('gf_random_num', 'P') is not null)
    drop proc gf_random_num
go
create proc gf_random_num(
    @small int=0,
    @big int=100
)
as
    select Round(RAND()*(@big-@small),0)+ @small random_num ;
go

-- 查看表字段结构
IF (object_id('gf_table_info') is not null)
	drop proc gf_table_info
GO
create proc gf_table_info(
	@table_name varchar(max)
)
AS
SELECT syscolumns.name, systypes.name AS type, syscolumns.isnullable, syscolumns.length, syscomments. TEXT defaultvalue, CONVERT ( nvarchar (50), ISNULL( extended_properties. VALUE, '' )) express FROM syscolumns LEFT JOIN syscomments ON syscomments.id = syscolumns.cdefault LEFT JOIN sys.extended_properties extended_properties ON extended_properties.major_id = syscolumns.id AND syscolumns.colid = extended_properties.minor_id JOIN systypes ON syscolumns.xusertype = systypes.xusertype WHERE syscolumns.id =
object_id(@table_name)
GO

-- 3 查看长度
IF(object_id('gf_length','P') is not null)
	drop proc gf_length
go
create proc gf_length(
	@word varchar(max)
)
as
	select len(@word)
go


-- 4 获取数字列表
IF(object_id('gf_numbers','P') is not null)
	drop proc gf_numbers
go
create proc gf_numbers(
	@Times int = 10
)
as
DECLARE @I int
DECLARE @sql varchar(max)
SET @I = 1
set @sql = 'select '+cast(@I as varchar) + ' number '
set @I = 2
WHILE @I<= @Times
BEGIN
  set @sql = @sql + ' union all select ' + cast(@I as varchar)
  SET @I = @I + 1
END
exec (@sql)
go




exec gf_info